--region [dbo].[AddGroupType]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddGroupType]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddGroupType]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddGroupType]
GO

CREATE PROCEDURE [dbo].[AddGroupType]
	@GroupTypeName nvarchar(50),
	@DiseaseType int,
	@Introduction nvarchar(max),
	@Logo nvarchar(255),
	@GroupNum int
AS

INSERT INTO [GroupType] (
	[GroupTypeName],
	[DiseaseType],
	[Introduction],
	[Logo],
	[GroupNum]
) VALUES (
	@GroupTypeName,
	@DiseaseType,
	@Introduction,
	@Logo,
	@GroupNum
)

GO
--endregion


--region [dbo].[UpdateGroupType]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupType]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupType]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupType]
GO

CREATE PROCEDURE [dbo].[UpdateGroupType]
	@Id int,
	@GroupTypeName nvarchar(50),
	@Introduction nvarchar(max),
	@Logo nvarchar(255)
AS

UPDATE [GroupType] SET
	[GroupTypeName] = @GroupTypeName,
	[Introduction] = @Introduction,
	[Logo] = @Logo
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[DelGroupType]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelGroupType]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelGroupType]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelGroupType]
GO

CREATE PROCEDURE [dbo].[DelGroupType]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[GroupType]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetGroupType]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupType]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupType]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupType]
GO

CREATE PROCEDURE [dbo].[GetGroupType]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[GroupTypeName],
	[DiseaseType],
	[Introduction],
	[Logo],
	[GroupNum]
FROM
	[GroupType]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetGroupTypeByDiseaseType]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupTypeByDiseaseType]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupTypeByDiseaseType]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupTypeByDiseaseType]
GO

CREATE PROCEDURE [dbo].[GetGroupTypeByDiseaseType]
	@DiseaseType int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[GroupTypeName],
	[DiseaseType],
	[Introduction],
	[Logo],
	[GroupNum]
FROM
	[GroupType]
WHERE
	[DiseaseType] = @DiseaseType

GO
--endregion


--region [dbo].[GetGroupTypeList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupTypeList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupTypeList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupTypeList]
GO

CREATE PROCEDURE [dbo].[GetGroupTypeList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [GroupType]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[GroupTypeName],
	[DiseaseType],
	[Introduction],
	[Logo],
	[GroupNum]
FROM
	[GroupType]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [GroupType] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


